package one

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"gorm.io/gorm/schema"
	"log"
	"os"
	"time"
)

// 单表查询

func Test() {
	fmt.Println("-----------单表查询---------------------")

	db, err := mysqlConnect()
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println(db)

	if err := db.AutoMigrate(&UserCase2111{}); err != nil {
		fmt.Println("自动创建表结构失败: ", err)
		return
	}

	//var u1 []UserCase2111 = []UserCase2111{
	//	{UserName: "张三", Sex: true, Age: 16, Hibby: "唱歌"},
	//	{UserName: "里斯", Sex: true, Age: 18, Hibby: "跳舞"},
	//	{UserName: "高斯", Sex: true, Age: 20, Hibby: "跳舞"},
	//	{UserName: "塞兰", Sex: true, Age: 22, Hibby: "唱歌"},
	//	{UserName: "路奇", Sex: true, Age: 12, Hibby: "唱歌"},
	//	{UserName: "马六", Sex: true, Age: 26, Hibby: "跳舞"},
	//	{UserName: "罗萨", Sex: true, Age: 22, Hibby: "跳舞"},
	//	{UserName: "李四", Sex: true, Age: 20, Hibby: "看书"},
	//	{UserName: "王五", Sex: true, Age: 12, Hibby: "看书"},
	//	{UserName: "小红", Sex: false, Age: 25, Hibby: "听书"},
	//	{UserName: "小蓝", Sex: false, Age: 24, Hibby: "唱歌"},
	//	{UserName: "小兰", Sex: false, Age: 34, Hibby: "看书"},
	//	{UserName: "小罗", Sex: false, Age: 24, Hibby: "看书"},
	//	{UserName: "小洛", Sex: false, Age: 34, Hibby: "跳舞"},
	//	{UserName: "小卡", Sex: false, Age: 28, Hibby: "跳舞"},
	//	{UserName: "小方", Sex: false, Age: 28, Hibby: "唱歌"},
	//}
	//fmt.Println(u1)
	//db.Create(&u1)

	var uArr1 []UserCase2111
	db.Find(&uArr1)
	for _, u := range uArr1 {
		fmt.Printf("u: %+v\n", u)
	}

	var u1 UserCase2111
	NewSingleQuery(db, &u1).Where("id = ?", 16).First()
	fmt.Printf("u1: %+v\n", u1)

	//查询user_name 和 sex的用户数据
	var u2 UserCase2111
	NewSingleQuery(db, &u2).
		Where("user_name = ?", "小红").
		Where("sex = ?", false).
		First()
	fmt.Printf("u2: %+v\n", u2)

	////查询user_name 和 sex的用户数据，返回指定泛型结构体
	//result, err := FirstTo[struct {
	//	UserName string `json:"user_name"`
	//	Sex      bool   `json:"sex"`
	//}](NewSingleQuery(db, &UserCase2111{}).
	//	Select("user_name, sex").
	//	Where("user_name = ?", "小红").
	//	Where("sex = ?", false))
	//if err != nil {
	//	fmt.Printf("查询错误: %v\n", err)
	//} else {
	//	fmt.Printf("result: %+v\n", result)
	//}

	//查询age < 18的用户数据，返回指定泛型结构体列表
	//results, err2 := FindTo[struct {
	//	ID       uint   `json:"id"`
	//	UserName string `json:"user_name"`
	//	Age      int    `json:"age"`
	//}](NewSingleQuery(db, &UserCase2111{}).
	//	Select("id", "user_name", "age").
	//	Where("age < ?", 18).
	//	OrderByDesc("id"))
	//if err2 != nil {
	//	fmt.Printf("查询错误: %v\n", err2)
	//} else {
	//	fmt.Printf("result: %+v\n", results)
	//}

	//查询age < 18的用户数据，返回指定泛型结构体列表
	res2, err2 := FindTo[FindAge1](NewSingleQuery(db, &UserCase2111{}).
		Select("id, user_name, age").
		Where("age < ?", 18).
		OrderByDesc("id"))
	if err2 != nil {
		fmt.Printf("查询错误: %v\n", err2)
	} else {
		fmt.Printf("result: %+v\n", res2)
	}

	//根据性别分组，并统计数量
	results, err3 := FindTo[findSexGroup1](NewSingleQuery(db, &UserCase2111{}).
		Select("sex, count(0) AS 'num'").
		GroupBy("sex"))
	if err3 != nil {
		fmt.Printf("查询错误: %v\n", err3)
	} else {
		fmt.Printf("result: %+v\n", results)
	}

	//根据爱好hibby分组 和 取每个爱好的平均年龄，平均年龄大于24
	res4, err4 := FindTo[findUserCase2111](NewSingleQuery(db, &UserCase2111{}).
		Select("hibby", "count(0) AS 'num'", "TRUNCATE(avg(age), 0) AS 'age'").
		GroupBy("hibby").Having("avg(age) > ?", 24))
	if err4 != nil {
		fmt.Printf("查询错误: %v\n", err4)
	} else {
		fmt.Printf("res4: %+v\n", res4)
	}

	//分页-根据爱好hibby分组 和 取每个爱好的平均年龄，平均年龄大于24
	res, resp, err5 := PaginateTo[findUserCase2111](NewSingleQuery(db, &UserCase2111{}).
		Select("hibby", "count(0) AS 'num'", "TRUNCATE(avg(age), 0) AS 'age'").
		GroupBy("hibby").Having("avg(age) > ?", 24).Page(2, 3))
	if err5 != nil {
		fmt.Printf("查询错误: %v\n", err5)
	} else {
		fmt.Printf("res: %+v\n", res)
		fmt.Printf("resp: %+v\n", resp)
	}

}

// 初始化连接
func mysqlConnect() (*gorm.DB, error) {
	url := "root:123456@tcp(127.0.0.1:3306)/case1?charset=utf8mb4&parseTime=True&loc=Local"

	// 配置自定义 Logger 打印sql日志
	newLogger := logger.New(
		log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
		logger.Config{
			SlowThreshold:             time.Second, // 慢 SQL 阈值
			LogLevel:                  logger.Info, // 可以设置为不同级别 Silent-不打印任何日志 Error-只打印错误日志 Warn-打印慢查询和错误 Info-打印慢查询和错误
			IgnoreRecordNotFoundError: true,        // 忽略ErrRecordNotFound（记录未找到）错误
			Colorful:                  true,        // 彩色打印
		},
	)

	db, err := gorm.Open(mysql.Open(url), &gorm.Config{
		Logger: newLogger, // 设置 Logger
		NamingStrategy: schema.NamingStrategy{
			SingularTable: true, // 关闭默认，使用表名
		},
	})
	if err != nil {
		return nil, fmt.Errorf("mysql connect err: %v", err)
	}

	sqlDB, err := db.DB()
	if err != nil {
		panic("failed to get sql.DB")
	}

	// 配置连接池
	// 最大空闲连接数 - 应该小于最大打开连接数
	sqlDB.SetMaxIdleConns(20)
	// 最大打开连接数 - 根据应用负载和MySQL配置调整
	sqlDB.SetMaxOpenConns(100)
	// 连接最大生命周期 - 小于MySQL的wait_timeout设置
	sqlDB.SetConnMaxLifetime(5 * time.Minute)
	// 空闲连接最大存活时间
	sqlDB.SetConnMaxIdleTime(10 * time.Minute)

	return db, nil
}

// 表结构体
type UserCase2111 struct {
	ID       int    `gorm:"primarykey;autoIncrement" json:"id"`
	UserName string `gorm:"type:varchar(50);not null" json:"user_name"`
	Sex      bool   `gorm:"type:tinyint(1);default(1)" json:"sex"`
	Age      int    `gorm:"type:int(5);" json:"age"`
	Hibby    string `gorm:"type:varchar(50);" json:"hibby"`
}

// 查询结果结构体
type findSexGroup1 struct {
	Sex bool `json:"sex"`
	Num int  `json:"num"`
}

type FindAge1 struct {
	ID       uint   `json:"id"`
	UserName string `json:"user_name"`
	Age      int    `json:"age"`
}

type findUserCase2111 struct {
	ID       int    `json:"id"`
	UserName string `json:"user_name"`
	Sex      bool   `json:"sex"`
	Age      int    `json:"age"`
	Hibby    string `json:"hibby"`
	Num      int    `json:"num"`
}
